{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Python自动翻译英语论文PDF"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***涉及技术：***\n",
    "1. Python读取PDF文本\n",
    "2. pandas的读取csv、多数据merge、输出Excel\n",
    "3. Python正则表达式实现英文分词"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. 读取PDF文本内容"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simple\n",
      "Requirement already satisfied: pdfplumber in d:\\anaconda3\\lib\\site-packages (0.5.16)\n",
      "Requirement already satisfied: chardet in d:\\anaconda3\\lib\\site-packages (from pdfplumber) (3.0.4)\n",
      "Requirement already satisfied: pdfminer.six==20200104 in d:\\anaconda3\\lib\\site-packages (from pdfplumber) (20200104)\n",
      "Requirement already satisfied: pillow>=7.0.0 in d:\\anaconda3\\lib\\site-packages (from pdfplumber) (7.0.0)\n",
      "Requirement already satisfied: unicodecsv>=0.14.1 in d:\\anaconda3\\lib\\site-packages (from pdfplumber) (0.14.1)\n",
      "Requirement already satisfied: six in d:\\anaconda3\\lib\\site-packages (from pdfplumber) (1.14.0)\n",
      "Requirement already satisfied: wand in d:\\anaconda3\\lib\\site-packages (from pdfplumber) (0.5.9)\n",
      "Requirement already satisfied: pycryptodome in d:\\anaconda3\\lib\\site-packages (from pdfplumber) (3.9.7)\n",
      "Requirement already satisfied: sortedcontainers in d:\\anaconda3\\lib\\site-packages (from pdfminer.six==20200104->pdfplumber) (2.1.0)\n"
     ]
    }
   ],
   "source": [
    "!pip install -i https://pypi.tuna.tsinghua.edu.cn/simple pdfplumber"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pdfplumber\n",
    "def read_pdf(pdf_fpath):\n",
    "    pdf = pdfplumber.open(pdf_fpath)\n",
    "    page_conts = []\n",
    "    for page in pdf.pages:\n",
    "        page_conts.append(page.extract_text())\n",
    "    pdf.close()\n",
    "    return \" \".join(page_conts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "pdf_fpath = \"D:/tmp/Wide & Deep Learning for Recommender Systems.pdf\"\n",
    "pdf_cont = read_pdf(pdf_fpath)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wide & Deep Learning for Recommender Systems\n",
      "Heng-Tze Cheng, Levent Koc, Jeremiah Harmsen, Tal Shaked, Tushar Chandra,\n",
      "Hrishi Aradhye, Glen Anderson, Greg Corrado, Wei Chai, Mustafa Ispir, Rohan Anil,\n",
      "Zakaria Haque, Lichan Hong, Vihan Jain, Xiaobing Liu, Hemal Shah\n",
      "∗\n",
      "GoogleInc.\n",
      "ABSTRACT\n",
      "have never or rarely occurred in the past. Recommenda-\n",
      "6\n",
      "tions based on memorization are usually more topical and\n",
      "1 Generalized linear models with nonlinear feature transfor-\n",
      "directly relevant to the items on which users have already\n",
      "0 mations are widely used for large-scale regression and clas-\n",
      "performed actions. Compared with memorization, general-\n",
      "2 siﬁcationproblemswithsparseinputs. Memorizationoffea-\n",
      "ization tends to improve the diversity of the recommended\n",
      "  tureinteractionsthroughawide setofcross-productfeature\n",
      "n items. Inthispaper,wefocusontheappsrecommendation\n",
      "transformationsareeﬀectiveandinterpretable,whilegener-\n",
      "u problemfortheGooglePlaystore,buttheapproachshould\n",
      "alizationrequiresmorefeatureengineeringeﬀort. Withless\n",
      "J apply to generic recommender systems.\n",
      "  featureengineering,deepneuralnetworkscangeneralizebet-\n",
      "4 Formassive-scaleonlinerecommendationandrankingsys-\n",
      "tertounseenfeaturecombinationsthroughlow-dimensional\n",
      "2 temsinanindustrialsetting,generalizedlinearmodelssuch\n",
      "denseembeddingslearnedforthesparsefeatures. However,\n",
      "  as logistic regression are widely used because they are sim-\n",
      "  deep neural networks with embeddings can over-generalize\n",
      "] ple,scalableandinterpretable. Themodelsareoftentrained\n",
      "G andrecommendlessrelevantitemswhentheuser-iteminter-\n",
      "onbinarizedsparsefeatureswithone-hotencoding. E.g.,the\n",
      "actions are sparse and high-rank. In this paper, we present\n",
      "L binary feature“user_installed_app=netflix”has value 1\n",
      "Wide & Deep learning—jointly trained wide linear models\n",
      ". if the user installed Netﬂix. Memorization can be achieved\n",
      "s anddeepneuralnetworks—tocombinethebeneﬁtsofmem-\n",
      "c orization and generalization for recommender systems. We eﬀectively using cross-product t\n"
     ]
    }
   ],
   "source": [
    "print(pdf_cont[:2000])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. 读取英语-汉语翻译词典文件"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "词典文件来自：https://github.com/skywind3000/ECDICT\n",
    "使用步骤：\n",
    "1. 下载代码打包：https://github.com/skywind3000/ECDICT/archive/master.zip\n",
    "2. 解压master.zip，然后解压其中的‪stardict.csv文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "d:\\Anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3063: DtypeWarning: Columns (11) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "# 注意：stardict.csv的地址需要替换成你自己的文件地址\n",
    "df_dict = pd.read_csv(\"D:/tmp/ECDICT-master/stardict.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3402564, 13)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dict.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>word</th>\n",
       "      <th>phonetic</th>\n",
       "      <th>definition</th>\n",
       "      <th>translation</th>\n",
       "      <th>pos</th>\n",
       "      <th>collins</th>\n",
       "      <th>oxford</th>\n",
       "      <th>tag</th>\n",
       "      <th>bnc</th>\n",
       "      <th>frq</th>\n",
       "      <th>exchange</th>\n",
       "      <th>detail</th>\n",
       "      <th>audio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>801655</th>\n",
       "      <td>design height</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>设计高度</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2739800</th>\n",
       "      <td>shibu</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[网络] 方回春堂；喊吧</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1232187</th>\n",
       "      <td>genus Testudo</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[网络] Testudo属</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>s:genus testudoes</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2403094</th>\n",
       "      <td>profit-and-loss statements</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[会计] 损益表</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0:profit-and-loss statement/1:s</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1197174</th>\n",
       "      <td>gain limited sensitivity</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>极限增益灵敏度</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               word phonetic definition    translation  pos  \\\n",
       "801655                design height      NaN        NaN           设计高度  NaN   \n",
       "2739800                       shibu      NaN        NaN   [网络] 方回春堂；喊吧  NaN   \n",
       "1232187               genus Testudo      NaN        NaN  [网络] Testudo属  NaN   \n",
       "2403094  profit-and-loss statements      NaN        NaN       [会计] 损益表  NaN   \n",
       "1197174    gain limited sensitivity      NaN        NaN        极限增益灵敏度  NaN   \n",
       "\n",
       "         collins  oxford  tag  bnc  frq                         exchange  \\\n",
       "801655       NaN     NaN  NaN  NaN  NaN                              NaN   \n",
       "2739800      NaN     NaN  NaN  NaN  NaN                              NaN   \n",
       "1232187      NaN     NaN  NaN  0.0  0.0                s:genus testudoes   \n",
       "2403094      NaN     NaN  NaN  NaN  NaN  0:profit-and-loss statement/1:s   \n",
       "1197174      NaN     NaN  NaN  NaN  NaN                              NaN   \n",
       "\n",
       "        detail  audio  \n",
       "801655     NaN    NaN  \n",
       "2739800    NaN    NaN  \n",
       "1232187    NaN    NaN  \n",
       "2403094    NaN    NaN  \n",
       "1197174    NaN    NaN  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dict.sample(10).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>word</th>\n",
       "      <th>translation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>'a</td>\n",
       "      <td>na. 一\\nn. 英文字母表的第一字母；【乐】A音\\nart. 冠以不定冠词主要表示类别\\...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>'A' game</td>\n",
       "      <td>[网络] 游戏；一个游戏；一局</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>'Abbāsīyah</td>\n",
       "      <td>[地名] 阿巴西耶 ( 埃 )</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>'Abd al Kūrī</td>\n",
       "      <td>[地名] 阿卜杜勒库里岛 ( 也门 )</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>'Abd al Mājid</td>\n",
       "      <td>[地名] 阿卜杜勒马吉德 ( 苏丹 )</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            word                                        translation\n",
       "0             'a  na. 一\\nn. 英文字母表的第一字母；【乐】A音\\nart. 冠以不定冠词主要表示类别\\...\n",
       "1       'A' game                                    [网络] 游戏；一个游戏；一局\n",
       "2     'Abbāsīyah                                    [地名] 阿巴西耶 ( 埃 )\n",
       "3   'Abd al Kūrī                                [地名] 阿卜杜勒库里岛 ( 也门 )\n",
       "4  'Abd al Mājid                                [地名] 阿卜杜勒马吉德 ( 苏丹 )"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 把word、translation之外的列扔掉\n",
    "df_dict = df_dict[[\"word\", \"translation\"]]\n",
    "df_dict.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. 英文分词和数据清洗"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Wide',\n",
       " '&',\n",
       " 'Deep',\n",
       " 'Learning',\n",
       " 'for',\n",
       " 'Recommender',\n",
       " 'Systems',\n",
       " 'Heng',\n",
       " 'Tze',\n",
       " 'Cheng']"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 分词\n",
    "import re\n",
    "word_list = re.split(\"\"\"[ ,.\\(\\)/\\n|\\-:=\\$\\[\"']\"\"\", pdf_cont)\n",
    "word_list[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['wide',\n",
       " 'deep',\n",
       " 'learning',\n",
       " 'for',\n",
       " 'recommender',\n",
       " 'systems',\n",
       " 'heng',\n",
       " 'tze',\n",
       " 'cheng',\n",
       " 'levent',\n",
       " 'koc',\n",
       " 'jeremiah',\n",
       " 'harmsen',\n",
       " 'tal',\n",
       " 'shaked',\n",
       " 'tushar',\n",
       " 'chandra',\n",
       " 'hrishi',\n",
       " 'aradhye',\n",
       " 'glen']"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数据清洗\n",
    "word_list_clean = []\n",
    "for word in word_list:\n",
    "    word = str(word).lower().strip()\n",
    "    # 过滤掉空词、数字、单个字符的词、停用词\n",
    "    if not word or word.isnumeric() or len(word)<=1:\n",
    "        continue\n",
    "    word_list_clean.append(word)\n",
    "word_list_clean[:20]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. 分词结果构造成一个DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>word</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>wide</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>deep</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>learning</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>for</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>recommender</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          word\n",
       "0         wide\n",
       "1         deep\n",
       "2     learning\n",
       "3          for\n",
       "4  recommender"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_words = pd.DataFrame({\n",
    "    \"word\": word_list_clean\n",
    "})\n",
    "df_words.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2322, 1)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_words.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>word</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>804</th>\n",
       "      <td>the</td>\n",
       "      <td>128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>and</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>546</th>\n",
       "      <td>of</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>503</th>\n",
       "      <td>model</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>939</th>\n",
       "      <td>wide</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>374</th>\n",
       "      <td>in</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>203</th>\n",
       "      <td>deep</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>405</th>\n",
       "      <td>is</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>286</th>\n",
       "      <td>for</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>845</th>\n",
       "      <td>to</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      word  count\n",
       "804    the    128\n",
       "57     and     67\n",
       "546     of     46\n",
       "503  model     41\n",
       "939   wide     36\n",
       "374     in     36\n",
       "203   deep     35\n",
       "405     is     31\n",
       "286    for     30\n",
       "845     to     29"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 统计词频\n",
    "df_words = (\n",
    "    df_words\n",
    "    .groupby(\"word\")[\"word\"]\n",
    "    .agg(count=\"size\")\n",
    "    .reset_index()\n",
    "    .sort_values(by=\"count\", ascending=False)\n",
    ")\n",
    "df_words.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. 和单词词典实现merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merge = pd.merge(\n",
    "    left = df_dict,\n",
    "    right = df_words,\n",
    "    left_on = \"word\",\n",
    "    right_on = \"word\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>word</th>\n",
       "      <th>translation</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>account</td>\n",
       "      <td>n. 报告, 解释, 估价, 理由, 利润, 算账, 帐目\\nvi. 报帐, 解释, 导致,...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>380</th>\n",
       "      <td>prediction</td>\n",
       "      <td>n. 预言, 预报\\n[化] 预测</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>generalization</td>\n",
       "      <td>n. 一般化, 普遍化, 概括\\n[化] 推广; 普适化</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>burget</td>\n",
       "      <td>[人名] 伯吉特</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>372</th>\n",
       "      <td>pipeline</td>\n",
       "      <td>n. 管道, 传递途径\\n[化] 管路; 管线</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>237</th>\n",
       "      <td>include</td>\n",
       "      <td>vt. 包括, 把...算入, 包住\\n[计] DOS内部命令:在CONFIG.SYS文件的...</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>524</th>\n",
       "      <td>threads</td>\n",
       "      <td>n. 线；相关串连；线程（thread的复数）</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>208</th>\n",
       "      <td>heng</td>\n",
       "      <td>n. 恒; 珩</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>capacity</td>\n",
       "      <td>n. 容量, 能力, 才能, 资格\\n[计] 容量</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228</th>\n",
       "      <td>important</td>\n",
       "      <td>a. 重要的, 有地位的, 大量的, 显要的, 自负的\\n[计] 要点</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               word                                        translation  count\n",
       "1           account  n. 报告, 解释, 估价, 理由, 利润, 算账, 帐目\\nvi. 报帐, 解释, 导致,...      1\n",
       "380      prediction                                  n. 预言, 预报\\n[化] 预测      2\n",
       "185  generalization                       n. 一般化, 普遍化, 概括\\n[化] 推广; 普适化      4\n",
       "56           burget                                           [人名] 伯吉特      1\n",
       "372        pipeline                            n. 管道, 传递途径\\n[化] 管路; 管线      1\n",
       "237         include  vt. 包括, 把...算入, 包住\\n[计] DOS内部命令:在CONFIG.SYS文件的...      2\n",
       "524         threads                            n. 线；相关串连；线程（thread的复数）      2\n",
       "208            heng                                            n. 恒; 珩      1\n",
       "62         capacity                          n. 容量, 能力, 才能, 资格\\n[计] 容量      1\n",
       "228       important                a. 重要的, 有地位的, 大量的, 显要的, 自负的\\n[计] 要点      2"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(607, 3)"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6. 存入Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merge.to_excel(\"./39. pdf_chinese_english.xlsx\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
